package com.zx.article.util;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.ResourceBundle;

public class DBUtils {

	private static ResourceBundle resourceBundle = ResourceBundle.getBundle("com.zx.article.util.db");
	private static String driver = resourceBundle.getString("driver");
//	加载驱动
	static {
		try {
			Class.forName(driver);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}

	/**
	 * 获得链接
	 * 
	 * @return
	 */
	public Connection getConnection() {
		String url = resourceBundle.getString("url");
		String username = resourceBundle.getString("user");
		String password = resourceBundle.getString("password");
		Connection connection = null;
		try {
			connection = DriverManager.getConnection(url, username, password);
		} catch (SQLException e) {
			System.err.println("获取链接对象失败");
		}
		return connection;
	}

	/**
	 * 回收资源
	 * 
	 * @param connection
	 * @param statement
	 */
	public void close(Connection connection, Statement statement) {
		close(connection, statement, null);
	}

	/**
	 * 回收资源
	 * 
	 * @param connection
	 * @param statement
	 * @param resultset
	 */
	public void close(Connection connection, Statement statement, ResultSet resultset) {
		try {
			if (resultset != null) {
				resultset.close();
			}
			if (statement != null) {
				statement.close();
			}
			if (connection != null) {
				connection.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * 更改操作 插入、修改、删除
	 * 
	 * @param sql
	 * @param vals
	 * @return
	 */
	public String update(String sql, Object[] vals) {
//		获得链接
		Connection connection = getConnection();
		if (connection == null) {
			System.err.print("链接失败");
			return null;
		}
//		预先状态集
		PreparedStatement prepareStatement = null;
		try {
//			预处理sql
			prepareStatement = connection.prepareStatement(sql);
//			填坑
			if (vals != null && vals.length != 0) {
				for (int i = 0; i < vals.length; i++) {
					prepareStatement.setObject(i + 1, vals[i]);
				}
			}
//			执行sql
			int executeUpdate = prepareStatement.executeUpdate();
//			返回结果
			return executeUpdate + "";
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
//			回收资源
			close(connection, prepareStatement);
		}
		return null;
	}

	public int queryCount(String sql, Object[] vals) {
//		获得链接
		Connection connection = getConnection();
		if (connection == null) {
			System.err.print("链接失败");
			return 0;
		}
//		预先状态集
		PreparedStatement prepareStatement = null;
		ResultSet resultSet = null;
		try {
//			预处理sql
			prepareStatement = connection.prepareStatement(sql);
//			填坑
			if (vals != null && vals.length != 0) {
				for (int i = 0; i < vals.length; i++) {
					prepareStatement.setObject(i + 1, vals[i]);
				}
			}
//			执行sql
			resultSet = prepareStatement.executeQuery();
//			返回结果
			if (resultSet.next()) {
				return resultSet.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
//			回收资源
			close(connection, prepareStatement, resultSet);
		}
		return 0;
	}

	public <T> T queryOne(String sql, Object[] vals, Class<T> cls, Map<String, String> fieldMap) {
//		获得链接
		Connection connection = getConnection();
		if (connection == null) {
			return null;
		}
//		预先状态集
		PreparedStatement prepareStatement = null;
		ResultSet resultSet = null;
		try {
			prepareStatement = connection.prepareStatement(sql);
//			补全sql代码
			if (vals != null && vals.length != 0) {
				for (int i = 0; i < vals.length; i++) {
					prepareStatement.setObject(i + 1, vals[i]);
				}
			}
//			执行sql获得结果集
			resultSet = prepareStatement.executeQuery();
			while (resultSet.next()) {
				T obj = cls.newInstance();
				for (Entry<String, String> entry : fieldMap.entrySet()) {
					Field declaredField = cls.getDeclaredField(entry.getKey());
					declaredField.setAccessible(true);
					declaredField.set(obj, resultSet.getObject(entry.getValue()));
				}
				return obj;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (InstantiationException e) {
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		} catch (NoSuchFieldException e) {
			e.printStackTrace();
		} catch (SecurityException e) {
			e.printStackTrace();
		}
		return null;
	}

	public <T> List<T> queryList(String sql, Object[] vals, Class<T> cls, Map<String, String> fieldMap) {
		List<T> list = new ArrayList<T>();
//		获得链接
		Connection connection = getConnection();
		if (connection == null) {
			return null;
		}
//		预先状态集
		PreparedStatement prepareStatement = null;
		ResultSet resultSet = null;
		try {
			prepareStatement = connection.prepareStatement(sql);
//			补全sql代码
			if (vals != null && vals.length != 0) {
				for (int i = 0; i < vals.length; i++) {
					prepareStatement.setObject(i + 1, vals[i]);
				}
			}
//			执行sql获得结果集
			resultSet = prepareStatement.executeQuery();
			while (resultSet.next()) {
				T obj = cls.newInstance();
				for (Entry<String, String> entry : fieldMap.entrySet()) {
					Field declaredField = cls.getDeclaredField(entry.getKey());
					declaredField.setAccessible(true);
					declaredField.set(obj, resultSet.getObject(entry.getValue()));
				}
				list.add(obj);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (InstantiationException e) {
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		} catch (NoSuchFieldException e) {
			e.printStackTrace();
		} catch (SecurityException e) {
			e.printStackTrace();
		} finally {
			close(connection, prepareStatement, resultSet);
		}
		return list;
	}

}
